﻿using System;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace ShopB2C.DBUtilities
{
    /// <summary>
    /// 分页数据库操作
    /// <remarks>
    /// MatchZhou - 2009/3/3
    /// </remarks>
    /// </summary>
    public class DbPaginations
    {

        #region Variables

        private int _pageIndex;
        private string _fields = "*", _tables, _primaryKey;

        #endregion

        #region Properties

        /// <summary>
        /// 当前页
        /// </summary>
        private int PageIndex
        {
            get { return this._pageIndex <= 0 ? 1 : this._pageIndex; }
            set { this._pageIndex = value; }
        }
        /// <summary>
        /// 每页显示记录数，默认 10
        /// </summary>
        private int PageSize { get; set; }
        /// <summary>
        /// 表名，如果多表操作，连接好再传入
        /// </summary>
        public string Tables
        {
            get { return this._tables; }
            set
            {
                if (string.IsNullOrEmpty(value))
                {
                    throw new Exception("表名是必须提供的");
                }
                this._tables = value;
            }
        }
        /// <summary>
        /// 字段列表，例：a,b,c
        /// </summary>
        public string Fields
        {
            get { return this._fields; }
            set { this._fields = string.IsNullOrEmpty(value) ? "*" : value; }
        }
        /// <summary>
        /// 条件，例：a and c or d
        /// </summary>
        public string Wheres { get; set; }
        /// <summary>
        /// 排序，例：order a asc,b desc
        /// </summary>
        public string Orders { get; set; }
        /// <summary>
        /// 主键
        /// </summary>
        public string PrimaryKey
        {
            get { return this._primaryKey; }
            set
            {
                if (string.IsNullOrEmpty(value))
                {
                    throw new Exception("主键必须提供");
                }
                this._primaryKey = value;
            }
        }
        /// <summary>
        /// 数据库类型
        /// </summary>
        public DatabaseTypeEnums DatabaseType { get; set; }

        #endregion

        #region Organize sql string

        // 组织 Sql 语句，OleDb
        private string OrganizeSqlStringOleDb()
        {
            string where = string.IsNullOrEmpty(this.Wheres) ? string.Empty : " WHERE " + this.Wheres;
            string order = string.IsNullOrEmpty(this.Orders) ? string.Empty : " ORDER BY " + this.Orders;
            // Access2000 中用 Select Top 要按关键字来筛选
            order += string.IsNullOrEmpty(order)
                ? string.Format(" ORDER BY {0} ASC", this.PrimaryKey)
                : string.Format(",{0} ASC", this.PrimaryKey);

            string s1 = "SELECT TOP {0} {1} FROM {2}{3}{4}";
            string s2 = "SELECT TOP {0} {1} FROM {2}{3}";
            string s3 = " WHERE {0} NOT IN ({1}){2}";
            string s4 = " AND {0} NOT IN ({1}){2}";

            int itop = (this.PageIndex - 1) * this.PageSize;
            s1 = string.Format(s1, itop, this.PrimaryKey, this.Tables, where, order);
            s2 = string.Format(s2, this.PageSize, this.Fields, this.Tables, where);
            s3 = string.Format(s3, this.PrimaryKey, s1, order);
            s4 = string.Format(s4, this.PrimaryKey, s1, order);
            string sqlString = string.Empty;

            sqlString = string.IsNullOrEmpty(where) ? s2 + s3 : s2 + s4;
            if (itop == 0) { sqlString = s2 + order; }
            return sqlString;
        }
        // 组织 Sql 语句，SqlServer
        private string OrganizeSqlStringSql()
        {
            string s1 = "SELECT TOP {0} {1} FROM {2}{3}{4}";
            string s2 = "SELECT TOP {0} {1} FROM {2}{3}";
            string s3 = " WHERE {0} NOT IN ({1}){2}";
            string s4 = " AND {0} NOT IN ({1}){2}";

            string where = string.IsNullOrEmpty(this.Wheres) ? string.Empty : " WHERE " + this.Wheres;
            string order = string.IsNullOrEmpty(this.Orders) ? string.Empty : " ORDER BY " + this.Orders;

            s1 = string.Format(s1, (this.PageIndex - 1) * this.PageSize, this.PrimaryKey, this.Tables, where, order);
            s2 = string.Format(s2, this.PageSize, this.Fields, this.Tables, where);
            s3 = string.Format(s3, this.PrimaryKey, s1, order);
            s4 = string.Format(s4, this.PrimaryKey, s1, order);

            return string.IsNullOrEmpty(where) ? s2 + s3 : s2 + s4;
        }
        // 组织返回总记录数 sql string
        private string OrganizeSqlStringGetRecordCount()
        {
            string where = string.IsNullOrEmpty(this.Wheres) ? string.Empty : " WHERE " + this.Wheres;
            string sqlString = "SELECT COUNT({0}) FROM {1}{2}";
            return string.Format(sqlString, this.PrimaryKey, this.Tables, where);
        }

        #endregion

        #region Methods

        /// <summary>
        /// 返回总记录数
        /// </summary>
        /// <returns>总记录数</returns>
        public int GetRecordCount()
        {
            object returnObject = 0;
            string sqlString = this.OrganizeSqlStringGetRecordCount();
            switch (this.DatabaseType)
            {
                case DatabaseTypeEnums.Access:
                    returnObject = OleDbHelper.OleDbText.ExecuteScalar(sqlString);
                    break;
                case DatabaseTypeEnums.SqlServer:
                    returnObject = SqlDbHelper.SqlDbText.ExecuteScalar(sqlString);
                    break;
            }
            return object.Equals(returnObject, null) ? 0 : (int)returnObject;
        }
        /// <summary>
        /// 返回 DataReader
        /// </summary>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <returns>DataReader</returns>
        public DbDataReader GetDataReader(int pageIndex, int pageSize)
        {
            this.PageIndex = pageIndex;
            this.PageSize = pageSize;
            switch (this.DatabaseType)
            {
                case DatabaseTypeEnums.Access:
                    return OleDbHelper.OleDbText.ExecuteReader(this.OrganizeSqlStringOleDb());
                case DatabaseTypeEnums.SqlServer:
                    return SqlDbHelper.SqlDbText.ExecuteReader(this.OrganizeSqlStringSql());
            }
            return null;
        }
        /// <summary>
        /// 返回 DataTable，Access 会使用 data adapter 分页
        /// </summary>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">每页显示记录数</param>
        /// <returns></returns>
        public DataTable GetDataTable(int pageIndex, int pageSize)
        {
            this.PageIndex = pageIndex;
            this.PageSize = pageSize;
            switch (this.DatabaseType)
            {
                case DatabaseTypeEnums.Access:
                    return this.QueryDataTableByAdapter();
                case DatabaseTypeEnums.SqlServer:
                    return SqlDbHelper.SqlDbText.QueryDataTable(this.OrganizeSqlStringSql());
            }
            return null;
        }
        // 通过 data adapter 来分页（access）
        private DataTable QueryDataTableByAdapter()
        {
            string where = string.IsNullOrEmpty(this.Wheres) ? string.Empty : " WHERE " + this.Wheres;
            string order = string.IsNullOrEmpty(this.Orders) ? string.Empty : " ORDER BY " + this.Orders;
            string adapterQueryString = string.Format("SELECT {0} FROM {1}{2}{3}", this.Fields, this.Tables, where, order);
            int pageIndex = this.PageIndex <= 1 ? 1 : this.PageIndex;

            DataTable dataTable = new DataTable();
            OleDbDataAdapter dataAdapterAccess = new OleDbDataAdapter(adapterQueryString, DbBase.ConnectionString);
            dataAdapterAccess.Fill((pageIndex - 1) * this.PageSize, this.PageSize, dataTable);
            return dataTable;
        }

        #endregion

    }
}